package contacts530.dao1.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import contacts530.db.Dbconnection;
import contacts530.po.Room;
import contacts530.po.Students;

public class RoomDaoImpl {
	String room_no;
	int room_id;
	String sql = "insert into room values(?,?)";
	PreparedStatement ps = null;
	private List<Students> studentList;

	/**
	 *新增通讯录
	 */
	public void save(Room room) {
		Connection conn=null;
		Dbconnection db = new Dbconnection();
		String room_no=room.getRoom_no();
		int room_id=room.getRoom_id();
		// TODO Auto-generated method stub
		String sql = "insert into student values(?,?)";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setInt(1, room_id);
			ps.setString(2, room_no);
			
			ps.executeUpdate();
		}catch(SQLException e) {
			
		}
			finally {
				db.close(ps, (PreparedStatement)null);
		}
	}

	/**
	 * 修改通讯录
	 */

	public void update(Room room) {
		Connection conn=null;
       String room_no=room.getRoom_no();
		int room_id=room.getRoom_id();
		String sql = "update room set room_no=?，room_id=?";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setString(1, room_no);
			ps.setInt(2, room_id);
			ps.executeUpdate();
		}catch(SQLException e) {
			
		}
			finally {
				Dbconnection db = null;
				db.close(ps, (PreparedStatement)null);
		}
	}// TODO Auto-generated method stub

	

	/**
	 *删除通讯录里的信息
	 *	 
	 **/

	public void update(int room_no, Room room) {
		Connection conn = Dbconnection.getConnection();
		String sql = "delete from room where room_no=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, room_no);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(ps, (PreparedStatement)null);
		}
	}// TODO Auto-generated method stub
//
//	
//
//	/**
//	 *根据房间名查询通讯录
//	 */
//
	public Room get() {
		Connection conn = Dbconnection.getConnection();
		List<Room> roomList = new ArrayList<Room>();
		String sql = "select room_id,room_no from room where room_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, room_id);
			rs = ps.executeQuery();
			while (rs.next()){
				int room_id = rs.getInt(1);
				String room_no= rs.getString(2);
				
				Room room = new Room(room_no, room_id);
				roomList.add(room);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(ps, (PreparedStatement) rs);
		}
		return roomList.get(0);	
	
 // TODO Auto-generated method stub
//		
	}
//	
//
//	/**
//	 *删除信息
//	 */
//
	public void delete(int room_no) {
		Connection conn = Dbconnection.getConnection();
		String sql = "delete from room where room=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, room_no);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(ps, (PreparedStatement)null);
		}
	}// TODO Auto-generated method stub
//
//	
//
	public List<Room> findAll() {
		Connection conn = Dbconnection.getConnection();
		List<Room> roomList = new ArrayList<Room>();
		String sql = "select room_id,room_no from room";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()){
				int room_id=rs.getInt(1);
				String room_no=rs.getString(2);
				
				Room room=new Room(room_no, room_id);
				
				roomList.add(room);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Dbconnection.close(ps, rs);
		}
		return roomList;
	}	// TODO Auto-generated method stub

}

